/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package mfi.com.model;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

/**
 *
 * @author Doodles
 */
public class DBConnection {
    
    private String hostname;
    private String username;
    private String password;
    private CallableStatement statement;
    private ResultSet result;
    private Connection connection;

    public DBConnection() {
        this.hostname  = "jdbc:mysql://localhost:3306/db_onlinedelivery";
        this.username  = "root";
        this.password  = "";
        this.statement = null;
        this.result    = null;
        this.connection = null;
    }
    
    public boolean connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            this.connection = DriverManager.getConnection(this.hostname, this.username, this.password);
            return true;
        }
        catch(Exception e) {
            return false;
        }
    }
    
    public boolean register(String email, String password, String fName, String lName, String bDate, String gender, String address, String contact) {
        
        this.result = null;
        
        try {
            
            if (this.connection == null) this.connect();
            
            if ( ! this.isValidEmail(email)) return false;
            
            this.statement = this.connection.prepareCall("{ CALL sp_members_insert(?, ?, ?, ?, ?, ?, ?, ?) }");
            
            this.statement.setString(1, email);
            this.statement.setString(2, password);
            this.statement.setString(3, fName);
            this.statement.setString(4, lName);
            this.statement.setString(5, bDate);
            this.statement.setString(6, gender);
            this.statement.setString(7, address);
            this.statement.setString(8, contact);
            
            this.result = this.statement.executeQuery();
            
            return this.result.next();
            
        }
        catch (Exception e) {
            return false;
        }
    }
    
    public ResultSet login(String email, String password) {
        try {
            
            if (this.connection == null) this.connect();
            
            this.statement = this.connection.prepareCall("{ CALL sp_members_login(?, ?) }");
            this.statement.setString(1, email);
            this.statement.setString(2, password);
            this.result = this.statement.executeQuery();
            
            return this.result.next() ? this.result:null;
            
        } catch (Exception e) {
            return null;
        }
    }
    
    public boolean isValidEmail(String email) {
        try {
            
            if (this.connection == null) this.connect();
            
            this.statement = this.connection.prepareCall("{ CALL sp_members_email_check(?) }");
            this.statement.setString(1, email);
            this.result = this.statement.executeQuery();
            return this.result.next() && result.getInt("total") == 0 ? true:false;
        } catch (Exception e) {
            return false;
        }
    }
    
}
